﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using Super.Core.IDBO;
using Super.Core.CodeHelper;

namespace Super.Core.DBObjects.SQL2005
{
    /// <summary>
    /// SQL2005数据库信息类；
    /// </summary>
    public class DbObject :IDbObject
    {

        /// <summary>
        ///  数据库类型
        /// </summary>
        public string DbType
        {
            get { return DatabaseTypeEnum.SQL2005.ToString(); }
        }

        private string _dbconnectStr;
        public string DbConnectStr
        {
            set { _dbconnectStr = value; }
            get { return _dbconnectStr; }
        }


        SqlConnection connect = new SqlConnection();

        #region "构造函数"
        public DbObject()
        {
        }
        public DbObject(string dbConnectStr)
        {
            _dbconnectStr = dbConnectStr;
            connect.ConnectionString = _dbconnectStr;
        }
        #endregion

        #region 打开数据库 OpenDB(string DbName)

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="DbName">要打开的数据库</param>
        /// <returns></returns>
        private SqlCommand OpenDB()
        {
            try
            {
                SqlCommand dbCommand = new SqlCommand();
                dbCommand.Connection = connect;
                if (connect.State == System.Data.ConnectionState.Closed)
                {
                    if (connect.ConnectionString == "")
                        connect.ConnectionString = _dbconnectStr;
                    connect.Open();
                }
                return dbCommand;

            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
                return null;
            }

        }
        #endregion


        #region ADO.NET 操作

        public int ExecuteSql(string SQLString)
        {
            SqlCommand dbCommand = OpenDB();
            dbCommand.CommandText = SQLString;
            int rows = dbCommand.ExecuteNonQuery();
            return rows;
        }
        public DataSet Query(string SQLString)
        {
            DataSet ds = new DataSet();
            try
            {
                OpenDB();
                SqlDataAdapter sda = new SqlDataAdapter(SQLString, connect);
                sda.Fill(ds, "ds");
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
        public SqlDataReader ExecuteReader( string strSQL)
        {
            try
            {
                OpenDB();
                SqlCommand cmd = new SqlCommand(strSQL, connect);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
        }
        public object GetSingle(string SQLString)
        {
            try
            {
                SqlCommand dbCommand = OpenDB();
                dbCommand.CommandText = SQLString;
                object obj = dbCommand.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch
            {
                return null;
            }
        }
        #endregion



        #region  得到数据库的所有用户表名
        /// <summary>
        /// 得到数据库的所有表名
        /// </summary>
        /// <param name="DbName">数据库</param>
        /// <returns></returns>
        public List<string> GetTables()
        {
            string strSql = "select [name] from sysobjects where xtype='U'and [name]<>'dtproperties' order by [name]";//order by id
            List<string> tabNames = new List<string>();
            SqlDataReader reader = ExecuteReader(strSql);
            while (reader.Read())
            {
                tabNames.Add(reader.GetString(0));
            }
            reader.Close();
            return tabNames;
        }

        #endregion


        #region  得到数据库的所有视图名   GetVIEWs()
        /// <summary>
        /// 得到数据库的所有视图名
        /// </summary>
        /// <param name="DbName">数据库</param>
        /// <returns></returns>
        public List<string> GetVIEWs()
        {
            string strSql = "select [name] from sysobjects where xtype='V' and [name]<>'syssegments' and [name]<>'sysconstraints' order by [name]";//order by id
            List<string> viewNames = new List<string>();
            SqlDataReader reader = ExecuteReader(strSql);
            while (reader.Read())
            {
                viewNames.Add(reader.GetString(0));
            }
            reader.Close();
            return viewNames;
        }
       
        #endregion


        #region 得到数据库的所有用户表名 GetTables()
        /// <summary>
        /// 得到数据库的所有用户表名 GetTables()
        /// </summary>
        /// <returns></returns>
        public List<TableInfo> GetTablesInfo()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sysobjects.[name] name,sysusers.name cuser,");
            strSql.Append("sysobjects.xtype type,sysobjects.crdate dates ");
            strSql.Append("from sysobjects,sysusers ");
            strSql.Append("where sysusers.uid=sysobjects.uid ");
            strSql.Append("and sysobjects.xtype='U' ");
            strSql.Append("and  sysobjects.[name]<>'dtproperties' ");
            strSql.Append("order by sysobjects.id");//order by id			
            //return Query(DbName, strSql.ToString()).Tables[0];

            List<TableInfo> tablist = new List<TableInfo>();
            TableInfo tab;
            SqlDataReader reader = ExecuteReader(strSql.ToString());
            while (reader.Read())
            {
                tab = new TableInfo();
                tab.TabName = reader.GetString(0);
                tab.TabDate = reader.GetValue(3).ToString();
                tab.TabType = reader.GetString(2);
                tab.TabUser = reader.GetString(1);
                tablist.Add(tab);
            }
            reader.Close();
            return tablist;
        }
        #endregion


        #region 得到数据库的所有视图信息
        /// <summary>
        /// 得到数据库的所有视图信息
        /// </summary>
        /// <returns></returns>
        public List<TableInfo> GetVIEWsInfo()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sysobjects.[name] name,sysusers.name cuser,");
            strSql.Append("sysobjects.xtype type,sysobjects.crdate dates ");
            strSql.Append("from sysobjects,sysusers ");
            strSql.Append("where sysusers.uid=sysobjects.uid ");
            strSql.Append("and sysobjects.xtype='V' ");
            strSql.Append("and sysobjects.[name]<>'syssegments' and sysobjects.[name]<>'sysconstraints'  ");
            strSql.Append("order by sysobjects.id");//order by id			
            //return Query(DbName, strSql.ToString()).Tables[0];

            List<TableInfo> tablist = new List<TableInfo>();
            TableInfo tab;
            SqlDataReader reader = ExecuteReader(strSql.ToString());
            while (reader.Read())
            {
                tab = new TableInfo();
                tab.TabName = reader.GetString(0);
                tab.TabDate = reader.GetValue(3).ToString();
                tab.TabType = reader.GetString(2);
                tab.TabUser = reader.GetString(1);
                tablist.Add(tab);
            }
            reader.Close();
            return tablist;
        }
        
        #endregion


        #region 得到(快速)数据库里表的列名和类型 GetColumnList(string TableName)
        /// <summary>
        /// 得到数据库里表或视图的列名和类型
        /// </summary>
        /// <param name="TableName">表</param>
        /// <returns></returns>
        public List<ColumnInfo> GetColumnList(string TableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select ");
                strSql.Append("a.colorder as colorder,");
                strSql.Append("a.name as ColumnName,");
                strSql.Append("b.name as TypeName ");
                strSql.Append(" from syscolumns a, systypes b, sysobjects c ");
                strSql.Append(" where a.xtype = b.xusertype ");
                strSql.Append("and a.id = c.id ");
                //strSql.Append("and c.xtype='U' ");
                strSql.Append("and c.name ='" + TableName + "'");
                strSql.Append(" order by a.colorder");

                List<ColumnInfo> collist = new List<ColumnInfo>();
                ColumnInfo col;
                SqlDataReader reader = ExecuteReader(strSql.ToString()); ;
                while (reader.Read())
                {
                    col = new ColumnInfo();
                    col.Colorder = reader.GetValue(0).ToString();
                    col.ColumnName = reader.GetString(1);
                    col.TypeName = reader.GetString(2);
                    col.Length = "";
                    col.Preci = "";
                    col.Scale = "";
                    col.IsPK = false;
                    col.cisNull = true;
                    col.DefaultVal = "";
                    col.IsIdentity = false;
                    col.DeText = "";
                    collist.Add(col);
                }
                reader.Close();
                return collist;

            }
            catch//(System.Exception ex)
            {
                return null;
            }

        }
       
        #endregion


        #region 得到表和视图的列的详细信息 GetColumnInfoList
        /// <summary>
        /// 得到数据库里表或视图的列的详细信息
        /// </summary>
        /// <param name="TableName">表</param>
        /// <returns></returns>
        public List<ColumnInfo> GetColumnInfoList( string TableName)
        {
           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("colorder=a.colorder,");
            strSql.Append("ColumnName=a.name,");
            strSql.Append("TypeName=b.name, ");
            //strSql.Append("Length=a.length, ");
            strSql.Append("Length=CASE WHEN b.name='nchar' THEN a.length/2 WHEN b.name='nvarchar' THEN a.length/2 ELSE a.length END,");
            strSql.Append("Preci=COLUMNPROPERTY(a.id,a.name,'PRECISION'), ");
            strSql.Append("Scale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), ");
            strSql.Append("IsIdentity=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,");
            strSql.Append("isPK=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (");
            strSql.Append("SELECT name FROM sysindexes WHERE indid in( ");
            strSql.Append("SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ");
            strSql.Append("))) then '√' else '' end, ");

            strSql.Append("cisNull=case when a.isnullable=1 then '√'else '' end, ");
            strSql.Append("defaultVal=isnull(e.text,''), ");
            strSql.Append("deText=isnull(g.[value],'') ");
            strSql.Append("FROM syscolumns a ");
            strSql.Append("left join systypes b on a.xusertype=b.xusertype ");
            strSql.Append("inner join sysobjects d on a.id=d.id  and (d.xtype='U' or d.xtype='V') and  d.name<>'dtproperties' ");
            strSql.Append("left join syscomments e on a.cdefault=e.id ");
            strSql.Append("left join sysproperties g on a.id=g.id and a.colid=g.smallid ");
            strSql.Append("where d.name='" + TableName + "' ");
            strSql.Append("order by a.id,a.colorder ");

            //return Query(DbName, strSql.ToString()).Tables[0];

            List<ColumnInfo> collist = new List<ColumnInfo>();
            ColumnInfo col;
            SqlDataReader reader = ExecuteReader(strSql.ToString()); ;
            while (reader.Read())
            {
                col = new ColumnInfo();
                col.Colorder = reader.GetValue(0).ToString();
                col.ColumnName = reader.GetString(1);
                col.TypeName = reader.GetString(2);
                col.Length = reader.GetValue(3).ToString();
                col.Preci = reader.GetValue(4).ToString();
                col.Scale = reader.GetValue(5).ToString();
                col.IsIdentity = (reader.GetString(6) == "√") ? true : false;
                col.IsPK = (reader.GetString(7) == "√") ? true : false;
                col.cisNull = (reader.GetString(8) == "√") ? true : false;
                col.DefaultVal = reader.GetString(9);
                col.DeText = reader.GetString(10);
                collist.Add(col);
            }
            reader.Close();
            return collist;
        }

        #endregion


        #region 得到数据库里表的主键 GetKeyName(string TableName)

        //创建列信息表
        public DataTable CreateColumnTable()
        {
            DataTable table = new DataTable();
            DataColumn col;

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "colorder";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "ColumnName";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "TypeName";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "Length";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "Preci";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "Scale";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "IsIdentity";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "isPK";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "cisNull";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "defaultVal";
            table.Columns.Add(col);

            col = new DataColumn();
            col.DataType = Type.GetType("System.String");
            col.ColumnName = "deText";
            table.Columns.Add(col);

            return table;
        }

        /// <summary>
        /// 得到数据库里表的主键
        /// </summary>
        /// <param name="TableName">表</param>
        /// <returns></returns>
        public DataTable GetKeyName( string TableName)
        {
            DataTable dtkey = CreateColumnTable();
        
            List<ColumnInfo> collist = GetColumnInfoList(TableName);
            DataTable dt = CodeCommon.GetColumnInfoDt(collist);
            DataRow[] rows = dt.Select(" isPK='√' or IsIdentity='√' ");
            foreach (DataRow row in rows)
            {
                DataRow nrow = dtkey.NewRow();
                nrow["colorder"] = row["colorder"];
                nrow["ColumnName"] = row["ColumnName"];
                nrow["TypeName"] = row["TypeName"];
                nrow["Length"] = row["Length"];
                nrow["Preci"] = row["Preci"];
                nrow["Scale"] = row["Scale"];
                nrow["IsIdentity"] = row["IsIdentity"];
                nrow["isPK"] = row["isPK"];
                nrow["cisNull"] = row["cisNull"];
                nrow["defaultVal"] = row["defaultVal"];
                nrow["deText"] = row["deText"];
                dtkey.Rows.Add(nrow);
            }
            return dtkey;


        }
        #endregion
        

        #region 得到数据表里的数据 GetTabData(string TableName)

        /// <summary>
        /// 得到数据表里的数据
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public DataTable GetTabData(string TableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [" + TableName + "]");
            return Query(strSql.ToString()).Tables[0];
        }
        /// <summary>
        /// 根据SQL查询得到数据表里的数据
        /// </summary>
        /// <param name="DbName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public DataTable GetTabDataBySQL( string strSQL)
        {
            return Query(strSQL).Tables[0];
        }

        #endregion


        #region 获得数据库的版本 GetVersion();
        /// <summary>
        /// 得到版本号
        /// </summary>
        /// <returns></returns>
        public string GetVersion()
        {
            try
            {
                string strSql = "execute master..sp_msgetversion ";//select @@version
                return Query(strSql).Tables[0].Rows[0][0].ToString();
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
                return "";
            }
        }
        #endregion





    }



}
